
  
  create PROC GetNumbProcessPerHour
	@BeginDate as datetime = '20130301',
	@EndDate as datetime='20130331', 
	@NumPocess as int OUTPUT
  as
  Begin
  
	SET NOCOUNT ON;
	/*** USAGE YCHOI 04/22/2013
		declare @Num as int
		exec GetNumbProcessPerHour '20130420', '20130419',  @Num  output
		select @Num;

		IF OBJECT_ID('GetNumbProcessPerHour', 'P') IS NOT NULL
		DROP PROC GetNumbProcessPerHour;
		GO

		go
	***/
	Declare @mBYear as int, @mBMonth as int, @mBDay as int, @mEYear as int, @mEMonth as int, @mEDay as int
	set @mBYear = YEAR(@BeginDate);
	set @mBMonth = month(@BeginDate);
	set @mBDay = day(@BeginDate);
	set @mEYear = YEAR(@EndDate);
	set @mEMonth = Month(@EndDate);
	set @mEDay = Day(@EndDate);
	select 
		 @mBYear as [Begin Year]
		,@mBMonth as [Begin Month]
		,@mBDay as [Begin day]
		,@mEYear as [End Year]
		,@mEMonth as [End Month]
		,@mEDay as [End Day];
	
	With [DateTimeAddedLogs]
	(
		[RecordID]
      ,[RecordStatus]
      ,[ProcessMethod]
      ,[TransactionType]
      ,[ServerName]
      ,[ClientName]
      ,[UserName]
      ,[ClientDateTime]
      ,[WhenReceived]
      ,[WhenProcess1]
      ,[ElapsedTime]
      ,[ClientSequence]
      ,[SetID]
      ,[ScriptID]
      ,[ItemID]
      ,[HostTransactionID]
      ,[ApplicationName]
      ,[FunctionName]
      ,[DataIn]
      ,[DataOut]
      ,[ErrorsOut]
      ,[SecurityID]
      ,[CallID]
      ,[UnitID]
      ,[EnvironmentID]
	  ,[MyClientDateTime]
	)
	as 
	(
	Select *, Convert(DateTime,  (Substring([ClientDateTime],1,4)+'-'+Substring ([ClientDateTime],5,2)+'-'
	+Substring([ClientDateTime],7,2)+' '+substring([ClientDateTime],9,2)+':'
	+substring([ClientDateTime],11,2)+':'+Substring([ClientDateTime],13,2)+'.'
	+Substring([ClientDateTime],15,3)), 21) as [MyClientDateTime]
	FROM 
		[dbo].[dsiTransactionLog]
	)
	Select 	
		 Convert(char(10), [MyClientDateTime], 101) as [Date]
		,DatePart(hour,[MyClientDateTime]) as [Hour]
		,Count(*) as [Counts per Hour]
		
	FROM 
		[DateTimeAddedLogs]
	where
	Convert( datetime, [MyClientDateTime], 101)  between @BeginDate and @EndDate
	Group By 	 Convert(char(10), [MyClientDateTime], 101), 	DatePart(hour,[MyClientDateTime])
	Order By Convert(char(10), [MyClientDateTime], 101)  ;

Select @NumPocess = COUNT(*)  
FROM 
		[dbo].[dsiTransactionLog]
	where
	Convert( datetime, SubString([ClientDateTime],1,4)+Substring([ClientDateTime],5,2)+substring([ClientDateTime],7,2), 112)  between @BeginDate and @EndDate
  END







/****** Script for SelectTopNRows command from SSMS 
SELECT  Distinct(SubString([ClientDateTime],7,2)) as mDay
		,Count(*)

  FROM [dcLINK42PD].[dbo].[dsiTransactionLog]
  where Substring([ClientDateTime],1,4 )='2013' and Substring([ClientDateTime],5,2)='04'
 
  Group By [ClientDateTime]
  Order By mDay Asc

	declare 	@BeginDate as datetime , @EndDate as datetime;
	set @BeginDate ='20130419';
	set @EndDate = '20130425';

	With [DateTimeAddedLogs]
	(
		[RecordID]
      ,[RecordStatus]
      ,[ProcessMethod]
      ,[TransactionType]
      ,[ServerName]
      ,[ClientName]
      ,[UserName]
      ,[ClientDateTime]
      ,[WhenReceived]
      ,[WhenProcess1]
      ,[ElapsedTime]
      ,[ClientSequence]
      ,[SetID]
      ,[ScriptID]
      ,[ItemID]
      ,[HostTransactionID]
      ,[ApplicationName]
      ,[FunctionName]
      ,[DataIn]
      ,[DataOut]
      ,[ErrorsOut]
      ,[SecurityID]
      ,[CallID]
      ,[UnitID]
      ,[EnvironmentID]
	  ,[MyClientDateTime]
	)
	as 
	(
	Select *, Convert(DateTime,  (Substring([ClientDateTime],1,4)+'-'+Substring ([ClientDateTime],5,2)+'-'
	+Substring([ClientDateTime],7,2)+' '+substring([ClientDateTime],9,2)+':'
	+substring([ClientDateTime],11,2)+':'+Substring([ClientDateTime],13,2)+'.'
	+Substring([ClientDateTime],15,3)), 21) as [MyClientDateTime]
	FROM 
		[dbo].[dsiTransactionLog]
	)
	Select 	
		 Convert(char(10), [MyClientDateTime], 101) as [Date]
		,DatePart(hour,[MyClientDateTime]) as [Hour]
		,Count(*) as [Counts per Hour]
		
	FROM 
		[DateTimeAddedLogs]
	where
	Convert( datetime, [MyClientDateTime], 101)  between @BeginDate and @EndDate
	Group By 	 Convert(char(10), [MyClientDateTime], 101), 	DatePart(hour,[MyClientDateTime])
	Order By Convert(char(10), [MyClientDateTime], 101)  ;



	
	declare 	@BeginDate as datetime , @EndDate as datetime;
	set @BeginDate ='20130419';
	set @EndDate = '20130425';

	With [DateTimeAddedLogs]
	(
		[RecordID]
      ,[RecordStatus]
      ,[ProcessMethod]
      ,[TransactionType]
      ,[ServerName]
      ,[ClientName]
      ,[UserName]
      ,[ClientDateTime]
      ,[WhenReceived]
      ,[WhenProcess1]
      ,[ElapsedTime]
      ,[ClientSequence]
      ,[SetID]
      ,[ScriptID]
      ,[ItemID]
      ,[HostTransactionID]
      ,[ApplicationName]
      ,[FunctionName]
      ,[DataIn]
      ,[DataOut]
      ,[ErrorsOut]
      ,[SecurityID]
      ,[CallID]
      ,[UnitID]
      ,[EnvironmentID]
	  ,[MyClientDateTime]
	)
	as 
	(
	Select *, Convert(DateTime,  (Substring([ClientDateTime],1,4)+'-'+Substring ([ClientDateTime],5,2)+'-'
	+Substring([ClientDateTime],7,2)+' '+substring([ClientDateTime],9,2)+':'
	+substring([ClientDateTime],11,2)+':'+Substring([ClientDateTime],13,2)+'.'
	+Substring([ClientDateTime],15,3)), 21) as [MyClientDateTime]
	FROM 
		[dbo].[dsiTransactionLog]
	)
	Select 	
		 DatePart(hour,[MyClientDateTime]) as [Hour]
		,AVG(Count(*) )as [Counts per Hour]
		
	FROM 
		[DateTimeAddedLogs]
	where
	Convert( datetime, [MyClientDateTime], 101)  between @BeginDate and @EndDate
	Group By 	 DatePart(hour,[MyClientDateTime])
	Order By DatePart(hour,[MyClientDateTime]) ;




	Select count(*) ,[ClientDateTime]
	FROM 
		[dcLINK42PD].[dbo].[dsiTransactionLog]
	Where Convert( datetime, SubString([ClientDateTime],1,4)+Substring([ClientDateTime],5,2)+substring([ClientDateTime],7,2), 112)  between @BeginDate and @EndDate
Group by [ClientDateTime]
Order By Convert( datetime, SubString([ClientDateTime],1,4)+Substring([ClientDateTime],5,2)+substring([ClientDateTime],7,2), 112) 
  
  --Convert( datetime, SubString([ClientDateTime],1,4)+Substring([ClientDateTime],5,2)+substring([ClientDateTime],7,2), 112) as [MyClientDateTime]

	Select count(*) 
	FROM 
		[dcLINK42PD].[dbo].[dsiTransactionLog]
	Where [ClientDateTime] >= '20130408000000000' and [ClientDateTime] <= '20130421999999999'

	
IF OBJECT_ID('GetNumbProcess', 'P') IS NOT NULL
DROP PROC GetNumbProcessPerHour;
GO

 
***/

